"""数据库连接和初始化模块"""
import pymysql
from app.config import config
from werkzeug.security import generate_password_hash

def get_db_connection():
    """获取数据库连接"""
    try:
        connection = pymysql.connect(
            host=config.MYSQL_HOST,
            user=config.MYSQL_USER,
            password=config.MYSQL_PASSWORD,
            database=config.MYSQL_DB,
            port=config.MYSQL_PORT,
            cursorclass=pymysql.cursors.DictCursor
        )
        return connection
    except pymysql.MySQLError as e:
        print(f"数据库连接错误: {e}")
        raise e

def init_db():
    """初始化数据库表"""
    from app.models.barcode import convert_to_ean13
    
    connection = None
    cursor = None
    try:
        connection = get_db_connection()
        cursor = connection.cursor()
        
        # 创建users表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                password_hash VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建默认管理员用户
        cursor.execute("SELECT COUNT(*) as count FROM users WHERE username = 'admin'")
        result = cursor.fetchone()
        if result['count'] == 0:
            password_hash = generate_password_hash('admin')
            cursor.execute(
                "INSERT INTO users (username, password_hash) VALUES (%s, %s)",
                ('admin', password_hash)
            )
            print("默认管理员用户创建成功: 用户名=admin, 密码=admin")
        
        # 创建products表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                barcode VARCHAR(50) NOT NULL UNIQUE,
                name VARCHAR(100) NOT NULL,
                price DECIMAL(10,2) NOT NULL,
                unit VARCHAR(20) NOT NULL,
                stock INT NOT NULL DEFAULT 0,
                expiry_date DATE NULL COMMENT '商品失效期'
            )
        ''')
        
        # 转换现有条码为EAN-13标准
        convert_to_ean13()
        
        # 创建store_settings表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS store_settings (
                id INT AUTO_INCREMENT PRIMARY KEY,
                store_name_zh VARCHAR(100) NOT NULL,
                store_name_uy VARCHAR(100) NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建transactions表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS transactions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                transaction_no VARCHAR(50) NOT NULL UNIQUE,
                transaction_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                total_amount DECIMAL(10,2) NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'completed',
                operator VARCHAR(50) DEFAULT 'admin'
            )
        ''')
        
        # 添加operator字段（如果表已存在但没有该字段）
        cursor.execute("SHOW COLUMNS FROM transactions LIKE 'operator'")
        if cursor.fetchone() is None:
            cursor.execute("ALTER TABLE transactions ADD COLUMN operator VARCHAR(50) DEFAULT 'admin'")
            cursor.execute("UPDATE transactions SET operator = 'admin'")
            print("已为transactions表添加operator字段并为历史记录填充admin")
        
        # 创建transaction_details表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS transaction_details (
                id INT AUTO_INCREMENT PRIMARY KEY,
                transaction_id INT NOT NULL,
                barcode VARCHAR(50) NOT NULL,
                product_name VARCHAR(100) NOT NULL,
                price DECIMAL(10,2) NOT NULL,
                quantity INT NOT NULL,
                subtotal DECIMAL(10,2) NOT NULL,
                FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
            )
        ''')
        
        # 确保store_settings表有一行数据
        cursor.execute('SELECT COUNT(*) as count FROM store_settings')
        result = cursor.fetchone()
        if result['count'] == 0:
            cursor.execute('''
                INSERT INTO store_settings (store_name_zh, store_name_uy)
                VALUES ('伊善齐商店', 'ئىشەنچ دۇكان')
            ''')
        
        # 创建系统更新表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS system_updates (
                id INT AUTO_INCREMENT PRIMARY KEY,
                version VARCHAR(20) NOT NULL,
                description TEXT NOT NULL,
                update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                is_active BOOLEAN DEFAULT TRUE
            )
        ''')
        
        # 确保系统更新表有初始版本数据
        cursor.execute('SELECT COUNT(*) as count FROM system_updates')
        result = cursor.fetchone()
        if result['count'] == 0:
            cursor.execute('''
                INSERT INTO system_updates (version, description)
                VALUES ('v1.0.0', '系统初始版本')
            ''')
        
        connection.commit()
        print("数据库初始化完成")
    except Exception as e:
        print(f"数据库初始化错误: {e}")
        if connection:
            connection.rollback()
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()

